package aos.ydksxt.service.cxfxtj;

import aos.framework.core.typewrap.Dto;
import aos.framework.core.typewrap.Dtos;
import aos.framework.core.utils.AOSJson;
import aos.framework.core.utils.AOSUtils;
import aos.framework.web.router.HttpModel;
import aos.ydksxt.service.common.CommonService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLDecoder;
import java.util.List;
import java.util.Map;

/**
 * 房产信息查询服务
 * Created by stiofan on 2017/2/2.
 */
@Service
public class FcxxcxfxService extends CommonService {
    @Override
    @Transactional
    public void initView(HttpModel httpModel) {
        httpModel.setViewPath("ydksxt/cxfxtj/fcxxcxfx.jsp");
    }

    @Transactional
	public void queryFcxxcxfxByParams(HttpModel httpModel) {
        String querySql = "select nvl(kz.shxydm, ry.nsrsbh) nsrsbh,\n" +
                "       ry.nsrmc,\n" +
                "       ry.lxrxm,\n" +
                "       ry.lxrsfzjlx_dm,\n" +
                "       ry.lxrsfzjhm,\n" +
                "       ry.lxryddh,\n" +
                "       ry.lxrdz_xzqhds_dm,\n" +
                "       ry.lxrdz_xzqhxq_dm,\n" +
                "       ry.lxrdz_jdxz_dm,\n" +
                "       ry.lxrdz_sqjq_dm,\n" +
                "       ry.lxrdz_dlxd_dm,\n" +
                "       ry.lxrdz_lh,\n" +
                "       fc.fcbh,\n" +
                "       fc.fczh,\n" +
                "       fc.fcmc,\n" +
                "       fc.jzmj,\n" +
                "       fc.xzqhds_dm,\n" +
                "       fc.xzqhxq_dm,\n" +
                "       fc.jdxz_dm,\n" +
                "       fc.zldz_sqjq_dm,\n" +
                "       fc.zldz_dlxd_dm,\n" +
                "       fc.zldz_lh,\n" +
                "       fc.fcyt_dm,\n" +
                "       fc.fcjg_dm,\n" +
                "       fc.fczyz,\n" +
                "       fc.fcqdrq,\n" +
                "       fc.fcyxqq,\n" +
                "       fc.fcyxqz,\n" +
                "       syq.fcsyrlx_dm,\n" +
                "       syq.fcsyqlx_dm,\n" +
                "       syq.fcsyq_qsrq,\n" +
                "       syq.fcsyq_zzrq,\n" +
                "       syq.zfr_dm,\n" +
                "       syq.zfrq,\n" +
                "       syq.zfyy,\n" +
                "       syq.fcsyq_zsbh,\n" +
                "       syq.fcsyq_qdrq,\n" +
                "       syq.fcsyq_ysmj,\n" +
                "       syq.fcsyq_msmj\n" +
                "  from ydks.dj_ryxx      ry,\n" +    //人员基础信息
                "       ydks.dj_ryxx_kz   kz,\n" +    //人员扩展信息
                "       ydks.dj_fcjcxx    fc,\n" +    //房产基础信息
                "       ydks.dj_fcsyqdjxx syq\n" +    //房产使用权登记信息
                " where ry.djxh = kz.djxh\n" +
                "   and ry.djxh = syq.djxh\n" +
                "   and fc.fcbh = syq.fcbh\n" +
                "   and ry.yxbz = 'Y'\n" +
                "   and fc.yxbz = 'Y'\n";
        Dto inDto = httpModel.getInDto();
        Dto params = Dtos.newDto();
        if (StringUtils.isNotEmpty(inDto.getString("nsrsbh"))) {
            params.put("nsrsbh", inDto.getString("nsrsbh"));
            querySql += "   and (ry.nsrsbh = :nsrsbh or kz.shxydm = :nsrsbh or kz.zzjgdm = :nsrsbh)\n";
        }
        if (StringUtils.isNotEmpty(inDto.getString("cxqsrq"))) {
            params.put("cxqsrq", inDto.getDate("cxqsrq"));
            querySql += "   and syq.fcsyq_qsrq >= :cxqsrq\n";
        }
        if (StringUtils.isNotEmpty(inDto.getString("cxzzrq"))) {
            params.put("cxzzrq", inDto.getDate("cxzzrq"));
            querySql += "   and syq.fcsyq_zzrq <= :cxzzrq\n";
        }
        if (StringUtils.isNotEmpty(inDto.getString("sfxszfxx"))) {
            if (inDto.getString("sfxszfxx").contains("Y") || inDto.getString("sfxszfxx").contains("N")) {
                params.put("sfxszfxx", inDto.getString("sfxszfxx"));
                querySql += "   and syq.yxbz = :sfxszfxx\n";
            }
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswjgDm"))) {
            params.put("zgswjgDm", inDto.get("zgswjgDm"));
            querySql += "   and ry.zgswjg_dm = :zgswjgDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("zgswskfjDm"))) {
            params.put("zgswskfjDm", inDto.get("zgswskfjDm"));
            querySql += "   and ry.zgswskfj_dm = :zgswskfjDm\n";
        }
        if (!StringUtils.isEmpty(inDto.getString("ssglyDm"))) {
            params.put("ssglyDm", inDto.get("ssglyDm"));
            querySql += "   and ry.ssgly_dm = :ssglyDm\n";
        }
        if(!StringUtils.isEmpty(inDto.getString("fczyz"))){
        	params.put("fczyz", inDto.get("fczyz"));
        	querySql += "  and fc.fczyz = :fczyz\n";
        }
        if(!StringUtils.isEmpty(inDto.getString("ysmj"))){
        	params.put("ysmj",inDto.get("ysmj"));
        	querySql += " and syq.fcsyq_ysmj = :ysmj\n" ;
        }
        if(!StringUtils.isEmpty(inDto.getString("msmj"))){
        	params.put("msmj",inDto.getString("msmj"));
        	querySql += " and syq.fcsyq_msmj = :msmj\n";
        }
        querySql += " order by syq.djxh,\n" +
                "          syq.fcbh,\n" +
                "          syq.fcsyqlx_dm,\n" +
                "          syq.fcsyq_qsrq,\n" +
                "          syq.fcsyq_zzrq";
        List results = persistenceDAO.findAllBySql(querySql, params);
        for (int i = 0; i < results.size(); i++) {
            Map map = (Map) results.get(i);
            map.put("lxrdz", getFullAddress((String) map.get("lxrdzXzqhdsDm"), (String) map.get("lxrdzXzqhxqDm"), (String) map.get("lxrdzJdxzDm"), (String) map.get("lxrdzSqjqDm"), (String) map.get("lxrdzDlxdDm"), (String) map.get("lxrdzLh")));
            map.put("zldz", getFullAddress((String) map.get("xzqhdszDm"), (String) map.get("xzqhxqDm"), (String) map.get("jdxzDm"), (String) map.get("zldzSqjqDm"), (String) map.get("zldzDlxdDm"), (String) map.get("zldzLh")));
        }
/*//        打印查询分析结果
        Dto outDto = Dtos.newOutDto();
        if (results.size() == 0) {
            outDto.setAppCode("noXX");
            outDto.setAppMsg("根据所选条件未能获得相关数据!");
        } else {
            try {
                InputStream inputStream = getClass().getResourceAsStream("金三房管信息比对分析模板.xlsx");
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                for (int i = 0; i < results.size(); i++) {
                    XSSFRow xssfRow = xssfSheet.getRow(i + 3);
                    if (xssfRow == null) {
                        if (xssfRow == null) {
                            xssfRow = xssfSheet.createRow(i + 3);
                            xssfRow.copyRowFrom(xssfSheet.getRow(3),new CellCopyPolicy());
                        }
                    }
                    Map map = (Map) results.get(i);
                    xssfRow.getCell(0).setCellValue(i + 1);
                    xssfRow.getCell(1).setCellValue(String.valueOf(map.get("nsrsbh")).replace("null",""));
                    xssfRow.getCell(2).setCellValue(String.valueOf(map.get("fcmc")).replace("null",""));
                    xssfRow.getCell(3).setCellValue(String.valueOf(map.get("nsrmc")).replace("null",""));
                    xssfRow.getCell(4).setCellValue(String.valueOf(map.get("tdzdh")).replace("null",""));
                    xssfRow.getCell(5).setCellValue(String.valueOf(map.get("fczh")).replace("null",""));
                    xssfRow.getCell(6).setCellValue(String.valueOf(map.get("jzmj")).replace("null",""));
                    xssfRow.getCell(7).setCellValue(String.valueOf(map.get("fgTdzdh")).replace("null",""));
                    xssfRow.getCell(8).setCellValue(String.valueOf(map.get("fgFczsbh")).replace("null",""));
                    xssfRow.getCell(9).setCellValue(String.valueOf(map.get("fgJzmj")).replace("null",""));
                    xssfRow.getCell(10).setCellValue(String.valueOf(map.get("fxjg")).replace("null",""));
                    XSSFCellStyle xssfCellStyle=xssfWorkbook.createCellStyle();
                    xssfCellStyle.cloneStyleFrom(xssfSheet.getRow(3).getCell(10).getCellStyle());
                    XSSFFont xssfFont=xssfWorkbook.createFont();
                    if ("异常".equals(String.valueOf(map.get("fxjg")).replace("null",""))) {
                        xssfFont.setColor(new XSSFColor(Color.RED));
                    } else {
                        xssfFont.setColor(new XSSFColor(Color.BLACK));
                    }
                    xssfCellStyle.setFont(xssfFont);
                    xssfRow.getCell(10).setCellStyle(xssfCellStyle);
                }
                //File file = new File(httpModel.getRequest().getServletContext().getRealPath("download"), "比对结果_" + httpModel.getUserModel().getAccount() + "_房管采集信息.xlsx");
                File file = new File(httpModel.getRequest().getServletContext().getRealPath("download"),
                		//URLEncoder.encode(strTest, "UTF-8");
                		 URLDecoder.decode(URLDecoder.decode("比对结果_","utf-8"),"utf-8") + httpModel.getUserModel().getAccount() + URLDecoder.decode(URLDecoder.decode("_房管采集信息.xlsx","utf-8"),"utf-8"));
                if (!file.exists()) {
                    file.createNewFile();
                } else {
                    file.delete();
                    file.createNewFile();
                }
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                xssfWorkbook.write(fileOutputStream);
                fileOutputStream.flush();
                fileOutputStream.close();
                xssfWorkbook.close();
                outDto.setAppCode("suc");
                outDto.put("downloadUrl", "download/" + file.getName());
               
            } catch (IOException e) {
                outDto.setAppCode("err");
                outDto.setAppMsg("执行比对分析时出现异常：" + e.getMessage());
                logger.error(e);
            }
        }*/
        httpModel.setOutMsg(AOSJson.toGridJson(AOSUtils.list2Map(results)));
    }
}
